#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
License 后端 + 用户管理（JWT + 角色权限）
运行：python app.py
访问：http://<ip>:5000
"""
import os, json, pandas as pd, requests, urllib.parse, hmac, hashlib, base64, sqlite3, jwt, datetime as dt
from datetime import timedelta
from flask import Flask, request, jsonify, send_file, send_from_directory
from flask_cors import CORS
from functools import wraps
from apscheduler.schedulers.background import BackgroundScheduler
import ntplib, subprocess
from io import BytesIO

# ---------- 路径 ----------
BASE_DIR   = os.path.dirname(os.path.abspath(__file__))
DB_LICENSE = os.path.join(BASE_DIR, '../data/licenses.db')   # Licese
DB_DING    = os.path.join(BASE_DIR, '../data/ding.db')       # 钉钉
SCHED_FILE = os.path.join(BASE_DIR, 'schedule.json')
LOG_FILE   = os.path.join(BASE_DIR, '../log/alarm.log')
FRONT_DIR  = os.path.join(BASE_DIR, '../frontend')
os.makedirs(os.path.dirname(LOG_FILE), exist_ok=True)

# ---------- Flask 应用初始化 ----------
app = Flask(__name__)
CORS(app)

# ---------- 原数据层 ----------
def _get_conn_license():
    return sqlite3.connect(DB_LICENSE, check_same_thread=False, isolation_level=None)

def _get_conn_ding():
    return sqlite3.connect(DB_DING, check_same_thread=False, isolation_level=None)

# ---------- 初始化时建表 ----------
def _init_table():
    # 1. license 库
    with _get_conn_license() as conn:
        conn.execute("""
        CREATE TABLE IF NOT EXISTS licenses (
            id       INTEGER PRIMARY KEY AUTOINCREMENT,
            customer TEXT NOT NULL,
            mac      TEXT NOT NULL,
            expiry   TEXT NOT NULL
        );
        """)
    # 2. ding 库
    with _get_conn_ding() as conn:
        conn.execute("""
        CREATE TABLE IF NOT EXISTS ding_robots (
            id     INTEGER PRIMARY KEY AUTOINCREMENT,
            name   TEXT NOT NULL,
            token  TEXT NOT NULL,
            secret TEXT NOT NULL,
            ctime  TEXT NOT NULL
        );
        """)
_init_table()

# ---------- 机器人 CRUD ----------
# 机器人 CRUD 全部换 _get_conn_ding()
def list_robots():
    return pd.read_sql("SELECT * FROM ding_robots ORDER BY id", _get_conn_ding())

def add_robot(name, token, secret):
    with _get_conn_ding() as conn:
        conn.execute(
            "INSERT INTO ding_robots(name,token,secret,ctime) VALUES (?,?,?,?)",
            (name, token, secret, dt.datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
        )

def update_robot(idx, name, token, secret):
    with _get_conn_ding() as conn:
        conn.execute(
            "UPDATE ding_robots SET name=?, token=?, secret=? WHERE id=?",
            (name, token, secret, idx)
        )

def del_robot(idx):
    with _get_conn_ding() as conn:
        conn.execute("DELETE FROM ding_robots WHERE id=?", (idx,))

def get_robot(idx: int):
    df = pd.read_sql("SELECT * FROM ding_robots WHERE id=?", _get_conn_ding(), params=(idx,))
    return None if df.empty else df.iloc[0].to_dict()

def default_robot():
    df = list_robots()
    return None if df.empty else df.iloc[0].to_dict()

# ---------- 用户管理 ----------

def list_licenses() -> pd.DataFrame:
    return pd.read_sql("SELECT id, customer, mac, expiry FROM licenses", _get_conn_license())

def add_license(customer: str, mac: str, expiry: str):
    with _get_conn_license() as conn:
        conn.execute("INSERT INTO licenses(customer, mac, expiry) VALUES (?,?,?)",
                     (customer.strip(), mac.strip(), expiry))

def update_license(idx: int, expiry: str):
    with _get_conn_license() as conn:
        conn.execute("UPDATE licenses SET expiry=? WHERE id=?", (expiry, idx))

def delete_license(idx: int):
    with _get_conn_license() as conn:
        conn.execute("DELETE FROM licenses WHERE id=?", (idx,))

# ---------- 日志 ----------
def write_log(msg: str):
    line = f"[{dt.datetime.now():%Y-%m-%d %H:%M:%S}] {msg}\n"
    print(line, end='')
    with open(LOG_FILE, 'a', encoding='utf-8') as f:
        f.write(line)

# ---------- 网络校时 ----------
def ntp_sync():
    write_log("🕐 开始网络校时...")
    try:
        client = ntplib.NTPClient()
        resp = client.request('pool.ntp.org', version=3)
        sh_time = dt.datetime.fromtimestamp(resp.tx_time)
        time_str = sh_time.strftime('%Y-%m-%d %H:%M:%S')
        subprocess.run(['date', '-s', time_str], check=True)
        write_log(f"✅ 网络校时成功（上海时间）：{time_str}")
    except Exception as e:
        write_log(f"❌ 网络校时失败：{e}")

# ---------- 钉钉 ----------
# ACCESS_TOKEN = "f777cc401699853ce189ef400c374d7434d631ceb628433734517e23d80fbb8c"
# DING_SECRET  = "SECf215744bd523e23df8784b4d67f8ae88c05b8c831b7e883bec0e19c10092b50a"
# WEBHOOK_URL  = f"https://oapi.dingtalk.com/robot/send?access_token={ACCESS_TOKEN}"

def gen_sign(secret: str) -> str:
    timestamp = str(round(dt.datetime.now().timestamp() * 1000))
    string_to_sign = f"{timestamp}\n{secret}"
    hmac_code = hmac.new(secret.encode(), string_to_sign.encode(), hashlib.sha256).digest()
    return f"&timestamp={timestamp}&sign={urllib.parse.quote_plus(base64.b64encode(hmac_code))}"

# ---------- 钉钉 ----------
def send_ding(title: str, md_text: str, robot_id: int = None):
    """
    发钉钉
    robot_id: 显式指定机器人主键；不传则取表内第一条
    """
    robot = get_robot(robot_id) if robot_id else default_robot()
    if not robot:
        write_log("❌ 没有可用的钉钉机器人")
        return

    token   = robot['token'].strip()
    secret  = robot['secret'].strip()
    if not token or not secret:
        write_log("❌ 机器人 token/secret 为空")
        return

    webhook = f"https://oapi.dingtalk.com/robot/send?access_token={token}"
    url     = webhook + gen_sign(secret)
    data    = {"msgtype": "actionCard",
               "actionCard": {"title": title,
                              "text": md_text,
                              "btnOrientation": "0",
                              "btns": [{"title": "详细信息",
                                        "actionURL": "https://alidocs.dingtalk.com/i/nodes/QPGYqjpJYrY0dbXAH245mjBR8akx1Z5N?utm_scene=person_space"}]}}
    try:
        rsp = requests.post(url, json=data, timeout=5)
        rsp.raise_for_status()
        write_log("钉钉推送成功")
    except Exception as e:
        write_log(f"钉钉推送失败：{e}")

# ---------- 调度 ----------
SCHEDULER = None

def load_schedule():
    if not os.path.exists(SCHED_FILE):
        return {"days": [1, 2, 3, 4, 5], "time": "09:00"}
    cfg = json.load(open(SCHED_FILE, encoding='utf-8'))
    cfg["days"] = [0 if d == 7 else d for d in cfg["days"]]
    return cfg

def save_schedule(data):
    json.dump(data, open(SCHED_FILE, 'w', encoding='utf-8'), ensure_ascii=False)

def reload_scheduler():
    global SCHEDULER
    if SCHEDULER:
        SCHEDULER.shutdown(wait=False)
    cfg = load_schedule()
    days = cfg["days"]
    hour, minute = map(int, cfg["time"].split(":"))
    SCHEDULER = BackgroundScheduler()
    SCHEDULER.add_job(job_alarm, 'cron', day_of_week=','.join(map(str, days)), hour=hour, minute=minute)
    SCHEDULER.start()
    write_log(f"调度重载成功：{cfg}")

def job_alarm():
    write_log("🚀 定时任务开始")
    df = list_licenses()
    if df.empty:
        write_log("❌ 数据库为空，跳过扫描")
        write_log("🏁 定时任务结束")
        return
    try:
        today = pd.Timestamp(dt.datetime.today().date())
        deadline = today + pd.Timedelta(days=3)
        df["结束日期"] = pd.to_datetime(df["expiry"], errors='coerce')
        expired = df[df["结束日期"] < today]
        will_expire = df[(df["结束日期"] >= today) & (df["结束日期"] <= deadline)]
        total = len(expired) + len(will_expire)
        write_log(f"已过期: {len(expired)} 行, 即将过期: {len(will_expire)} 行")
        if total == 0:
            write_log("无到期数据，不推送")
            return

        # ↓↓↓ 新增：读取本次要用的机器人 ↓↓↓
        cfg = load_schedule()                    # schedule.json 内容
        robot_id = cfg.get('robot_id')           # 没有就 None（走默认）
        robot = get_robot(robot_id) if robot_id else default_robot()
        if not robot:
            write_log("❌ 定时任务：没有可用的钉钉机器人")
            return
        if not robot['token'].strip() or not robot['secret'].strip():
            write_log("❌ 定时任务：机器人 token/secret 为空")
            return
        # ↑↑↑ 以上仅判断，不发钉 ↑↑↑

        md = ["🔔 <b>License 到期预警</b>  "]
        if not expired.empty:
            md.append("");md.append("🔴 <font color='#FF0000'><b>❌ 已过期（请立即处理）</b></font>")
            for _, r in expired.iterrows():
                md.append(f"- <font color='#FF0000'>客户：{r['customer']} | Mac：{r['mac']} | 到期：{r['结束日期'].strftime('%Y-%m-%d')}</font>")
            md.append("")
        if not will_expire.empty:
            md.append("🟠 <font color='#FF9800'><b>⏰ 即将过期（3 天内）</b></font>")
            for _, r in will_expire.iterrows():
                md.append(f"- <font color='#FF9800'>客户：{r['customer']} | Mac：{r['mac']} | 到期：{r['结束日期'].strftime('%Y-%m-%d')}</font>")
            md.append("")

        # ↓↓↓ 把 robot_id 传进去 ↓↓↓
        send_ding("License 定时预警", "  \n".join(md), robot_id)
        write_log("✅ 定时钉钉推送成功")
    except Exception as e:
        write_log(f"❌ 定时任务出错: {e}")
    write_log("🏁 定时任务结束")

# ---------- 工具 ----------
def alert_reason(expiry_str: str) -> str:
    try:
        exp = dt.datetime.strptime(expiry_str, '%Y-%m-%d').date()
    except Exception:
        return ''
    today = dt.datetime.today().date()
    if exp < today:
        return f"已过期 {(today - exp).days} 天"
    if exp <= today + timedelta(days=3):
        return f"即将过期（剩余 {(exp - today).days} 天）"
    return ''


# ---------- 原 License API ----------
def replace_license(idx: int, customer: str, mac: str, expiry: str):
    with _get_conn_license() as conn:
        conn.execute("UPDATE licenses SET customer=?, mac=?, expiry=? WHERE id=?",
                     (customer.strip(), mac.strip(), expiry, idx))

@app.route('/api/replace', methods=['POST'])
def api_replace():
    data = request.get_json()
    replace_license(int(data['idx']), data['customer'], data['mac'], data['expiry'])
    return jsonify({'ok': 1})                    

@app.route('/api/list', methods=['GET'])
def api_list():
    df = list_licenses()
    df['status'] = df['expiry'].apply(alert_reason)
    return jsonify(df.to_dict(orient='records'))

@app.route('/api/add', methods=['POST'])

def api_add():
    customer = request.form.get('customer', '').strip()
    mac = request.form.get('mac', '').strip()
    expiry = request.form.get('expiry', '').strip()
    if not all([customer, mac, expiry]):
        return jsonify({'ok': 0})
    add_license(customer, mac, expiry)
    return jsonify({'alert': alert_reason(expiry)})

@app.route('/api/update', methods=['POST'])

def api_update():
    data = request.get_json()
    idx = int(data['idx'])
    new_d = data['expiry']
    update_license(idx, new_d)
    return jsonify({'alert': alert_reason(new_d)})

@app.route('/api/delete', methods=['POST'])

def api_delete():
    idx = int(request.get_json()['idx'])
    delete_license(idx)
    return jsonify({'ok': 1})

@app.route('/api/schedule', methods=['GET'])
def api_schedule_get():
    cfg = load_schedule()
    return jsonify(cfg)

@app.route('/api/schedule', methods=['POST'])

def api_schedule_post():
    days = [0 if int(d) == 7 else int(d) for d in request.form.getlist('days')]
    time = request.form.get('time', '')
    save_schedule({"days": days, "time": time})
    write_log(f"报警时间已修改：每周{days} {time}")
    reload_scheduler()
    return '设置已保存并立即生效！'

@app.route('/api/log', methods=['GET'])
def api_log():
    if not os.path.exists(LOG_FILE):
        return ''
    with open(LOG_FILE, encoding='utf-8') as f:
        return f.read()

@app.route('/api/clear_log', methods=['POST'])

def api_clear_log():
    if os.path.exists(LOG_FILE):
        os.remove(LOG_FILE)
    return ''

@app.route('/api/export_excel', methods=['GET'])

def export_excel():
    df = list_licenses()
    df['status'] = df['expiry'].apply(alert_reason)
    buf = BytesIO()
    with pd.ExcelWriter(buf, engine='openpyxl') as writer:
        df.to_excel(writer, index=False, sheet_name='Licenses')
    buf.seek(0)
    return send_file(buf, mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
                     as_attachment=True, download_name='licenses.xlsx')

@app.route('/api/test_ding', methods=['POST'])

@app.route('/api/test_ding', methods=['POST'])
def test_ding():
    write_log("🚀 手动测试钉钉")
    df = list_licenses()
    if df.empty:
        write_log("❌ 数据库为空，跳过扫描")
        return '数据库为空，无内容可测', 400
    try:
        today = dt.datetime.now().date()
        deadline = today + timedelta(days=3)
        df['expiry'] = pd.to_datetime(df['expiry'], errors='coerce')
        expired = df[df['expiry'] < pd.Timestamp(today)]
        will_expire = df[(df['expiry'] >= pd.Timestamp(today)) & (df['expiry'] <= pd.Timestamp(deadline))]
        total = len(expired) + len(will_expire)
        if total == 0:
            write_log("无到期数据，不推送")
            return '暂无到期/即将到期 License，未发送', 200

        md = ["🔔 <b>License 到期预警</b>  "]
        if not expired.empty:
            md.append("");md.append("🔴 <font color='#FF0000'><b>❌ 已过期（请立即处理）</b></font>")
            for _, r in expired.iterrows():
                md.append(f"- <font>客户：{r['customer']} | Mac：{r['mac']} | 到期：{r['expiry'].strftime('%Y-%m-%d')}</font>")
            md.append("")
        if not will_expire.empty:
            md.append("🟠 <font color='#FF9800'><b>⏰ 即将过期（3 天内）</b></font>")
            for _, r in will_expire.iterrows():
                md.append(f"- <font>客户：{r['customer']} | Mac：{r['mac']} | 到期：{r['expiry'].strftime('%Y-%m-%d')}</font>")
            md.append("")

        # ↓↓↓ 读取前端传来的 robot_id ↓↓↓
        robot_id = request.get_json(silent=True).get('robot_id') if request.is_json else None
        send_ding("License 定时预警", "  \n".join(md), robot_id)
        write_log("✅ 手动钉钉推送成功")
    except Exception as e:
        write_log(f"❌ 手动测试出错: {e}")
    return '钉钉已发送，请检查群消息！', 200

@app.route('/api/ding', methods=['GET'])
def api_ding_list():
    return jsonify(list_robots().to_dict(orient='records'))

@app.route('/api/ding', methods=['POST'])
def api_ding_save():
    data = request.get_json()
    idx = int(data.get('id', 0))
    if idx == 0:
        add_robot(data['name'], data['token'], data['secret'])
    else:
        update_robot(idx, data['name'], data['token'], data['secret'])
    return jsonify({'ok': 1})

@app.route('/api/ding/<int:idx>', methods=['DELETE'])
def api_ding_del(idx):
    del_robot(idx)
    return jsonify({'ok': 1})

# ---------- 前端托管 ----------
@app.route('/')
@app.route('/<path:filename>')
def web_static(filename='index.html'):
    return send_from_directory(FRONT_DIR, filename)

# ---------- 启动 ----------
if __name__ == '__main__':
    ntp_sync()
    write_log("后端+Web+用户管理 服务启动")
    reload_scheduler()
    app.run(host='0.0.0.0', port=5000)

